﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class AbsenceTypeDAL : IAbsenceTypeDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
        public AbsenceTypeDAL()
        {

        }
        public IList<AbsenceTypeInfo> ListAbsenceTypes(AbsenceTypeInfo abInfo)
        {
            string strSQL;
            List<AbsenceTypeInfo> infoList = new List<AbsenceTypeInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@Sequence", abInfo.Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = @"SELECT t.ABSENCE_TYPE_SEQ, t.ABSENCE_TYPE_NAME
                         , t.REMARKS, t.RECORD_STATUS, t.CREATED_BY, t.UPDATED_BY, t.AppPrcId, ap.Name ApproveProcessName
                          FROM HRM_PS_ABSENCE_TYPES as t left join sysapproveprocess as ap on t.appprcid = ap.appprcid
                          WHERE t.RECORD_STATUS not like @DeleteRecord"
                         + (abInfo.Sequence > 0 ? " AND t.ABSENCE_TYPE_SEQ = @Sequence" : "");

                DataTable rdr = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach(DataRow dr in rdr.Rows)
                { 
                    AbsenceTypeInfo Info = new AbsenceTypeInfo(Convert.ToInt32(dr[0])
                                                               , Convert.ToString(dr[1])
                                                               , Convert.ToString(dr[2])
                                                               , Convert.ToString(dr[3])
                                                               , dr[4] == DBNull.Value ? (int?)null : Convert.ToInt32(dr[4])
                                                               , dr[5] == DBNull.Value ? (int?)null : Convert.ToInt32(dr[5])
                        )
                        {
                            ApproveProcessSeq =  dr[6] == DBNull.Value ? (int?)null : Convert.ToInt32(dr[6]),
                            ApproveProcessName = Convert.ToString(dr[7])
                        };
                    infoList.Add(Info);
                }

                return infoList;
            }
            catch
            { 
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
       
        public void Insert(AbsenceTypeInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@AbsenceTypeGroupSeq", Info.GroupSeq);
                dbManager.AddParameters(1, "@AbsenceTypeName", Info.Name); 
                dbManager.AddParameters(2, "@Remarks", Info.Remarks);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(5, "@CreatedWhen", DateTime.Today);
                dbManager.AddParameters(6, "@AppPrcId", Info.ApproveProcessSeq); 

                strSQL = "INSERT INTO HRM_PS_ABSENCE_TYPES(ABSENCE_TYPE_GROUP_SEQ, ABSENCE_TYPE_NAME"
                        + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN, AppPrcId)"
                    + " VALUES(@AbsenceTypeGroupSeq, @AbsenceTypeName"
                        + ", @Remarks, @RecordStatus, @CreatedBy, @CreatedWhen, @AppPrcId)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Update(AbsenceTypeInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@AbsenceTypeSeq", Info.Sequence);
                dbManager.AddParameters(1, "@AbsenceTypeGroupSeq", Info.GroupSeq);
                dbManager.AddParameters(2, "@AbsenceTypeName", Info.Name); 
                dbManager.AddParameters(3, "@Remarks", Info.Remarks);
                dbManager.AddParameters(4, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(5, "@UpdatedWhen", DateTime.Today);
                dbManager.AddParameters(6, "@AppPrcId", Info.ApproveProcessSeq);

                strSQL = "UPDATE HRM_PS_ABSENCE_TYPES"
                           + " SET ABSENCE_TYPE_GROUP_SEQ = @AbsenceTypeGroupSeq"
                                + ", ABSENCE_TYPE_NAME = @AbsenceTypeName" 
                                + ", REMARKS = @Remarks"
                                + ", UPDATED_BY = @UpdatedBy"
                                + ", UPDATED_WHEN = @UpdatedWhen"
                                + ", AppPrcId = @AppPrcId"
                           + " WHERE ABSENCE_TYPE_SEQ = @AbsenceTypeSeq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdateWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_ABSENCE_TYPES"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdateWhen"
                    + " WHERE ABSENCE_TYPE_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public Dictionary<int, string> ListApproveProcess()
        {
            Dictionary<int, string> result = new Dictionary<int, string>();
            string strSQL;
            try
            {
                dbManager.Open(); 
                
                strSQL = @"
(select top(1) 0 as AppPrcId, '(ใช้ค่าเริ่มต้น - ' + ap.Name + ')'  as Name 
	from SysApproveProcess ap where ap.AppPrcGrpId = 1 and ap.IsDefaultProcess = 1)
UNION
(	select ap.AppPrcId, ap.Name 
                            from SysApproveProcess ap
                            inner join SysApproveProcessGroup ag on ap.AppPrcGrpId = ag.AppPrcGrpId
                            where ag.AppPrcGrpId = 1
                            and ap.RecordStatus != 'D'  )";

                var dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach(DataRow dr in dt.Rows)
                {
                    result.Add(Convert.ToInt32(dr[0]), Convert.ToString(dr[1]));
                }
                dbManager.CommitTransaction();
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return result;
        }
    }
}
